/*
 * 
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
 * GNU General Public License for more details.
 * You should have received a copy of the GNU General Public License
 * along with this program. If not, see <http://www.gnu.org/licenses/>.
 */
package database;

import java.io.BufferedReader;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Map;

import org.apache.log4j.Logger;

/**
 * This class handles the database connection
 * 
 * 
 */
class MySQLConnectionHandler implements ConnectionHandler {
   private final Connection connection;
   private final String host;
   private final String dbName;
   private final String user;
   private final String password;

   MySQLConnectionHandler(final Map<String, String> dbInfo) {
	  host = dbInfo.get("dbHost");
	  dbName = dbInfo.get("dbName");
	  user = dbInfo.get("dbUsername");
	  password = dbInfo.get("dbPassword");
	  connection = load();
   }

   MySQLConnectionHandler(final String host, final String dbName, final String user, final String password) {
	  this.host = host;
	  this.dbName = dbName;
	  this.user = user;
	  this.password = password;
	  connection = load();
   }

   /**
    * Checks whether the current connection is allowed to create databases or setup the bot database
    * 
    * @return
    */
   @Override
   public boolean canCreateDB() {
	  PreparedStatement ps = null;
	  boolean out = false;
	  try {
		 ps = connection.prepareStatement("use " + dbName);
		 ps.executeUpdate();

		 try {
			ps.close();
		 } catch (final Exception e) {
		 }

		 ps = connection
			   .prepareStatement("CREATE TABLE testtesttest (testing int,CONSTRAINT pk_testtesttest PRIMARY KEY(testing)) ENGINE=InnoDB");
		 ps.executeUpdate();

		 try {
			ps.close();
		 } catch (final Exception e) {
		 }

		 ps = connection.prepareStatement("DROP TABLE testtesttest");
		 ps.executeUpdate();
		 out = true;
	  } catch (Exception e) {
		 try {
			ps = connection.prepareStatement("CREATE DATABASE " + dbName);
			ps.executeUpdate();

			try {
			   ps.close();
			} catch (final Exception e2) {
			}

			ps = connection.prepareStatement("use " + dbName);
			ps.executeUpdate();

			try {
			   ps.close();
			} catch (final Exception e2) {
			}

			ps = connection.prepareStatement("CREATE TABLE testtesttest (testing int,CONSTRAINT pk_testtesttest "
				  + "PRIMARY KEY(testing)) ENGINE=InnoDB");
			ps.executeUpdate();

			try {
			   ps.close();
			} catch (final Exception e2) {
			}

			ps = connection.prepareStatement("DROP DATABASE " + dbName);
			ps.executeUpdate();
			out = true;
		 } catch (SQLException e1) {
		 }
	  } finally {
		 try {
			ps.close();
		 } catch (Exception e) {
		 }
	  }
	  return out;
   }

   @Override
   public boolean clearForNewAge() {
	  PreparedStatement ps = null;
	  try {
		 ps = connection.prepareStatement("DELETE FROM kd");
		 ps.executeUpdate();

		 try {
			ps.close();
		 } catch (final Exception e2) {
		 }

		 ps = connection.prepareStatement("DELETE FROM somarmy");
		 ps.executeUpdate();

		 try {
			ps.close();
		 } catch (final Exception e2) {
		 }

		 ps = connection.prepareStatement("DELETE FROM aid");
		 ps.executeUpdate();

		 try {
			ps.close();
		 } catch (final Exception e2) {
		 }

		 ps = connection.prepareStatement("DELETE FROM province");
		 ps.executeUpdate();

		 try {
			ps.close();
		 } catch (final Exception e2) {
		 }

		 ps = connection.prepareStatement("DELETE FROM army");
		 ps.executeUpdate();

		 try {
			ps.close();
		 } catch (final Exception e2) {
		 }

		 ps = connection.prepareStatement("DELETE FROM actions");
		 ps.executeUpdate();

		 try {
			ps.close();
		 } catch (final Exception e2) {
		 }

		 ps = connection.prepareStatement("DELETE FROM target");
		 ps.executeUpdate();

		 try {
			ps.close();
		 } catch (final Exception e2) {
		 }

		 ps = connection.prepareStatement("DELETE FROM build");
		 ps.executeUpdate();

		 try {
			ps.close();
		 } catch (final Exception e2) {
		 }

		 ps = connection.prepareStatement("DELETE FROM nap");
		 ps.executeUpdate();

		 try {
			ps.close();
		 } catch (final Exception e2) {
		 }

		 ps = connection.prepareStatement("DELETE FROM attacks");
		 ps.executeUpdate();

		 try {
			ps.close();
		 } catch (final Exception e2) {
		 }

		 ps = connection.prepareStatement("DELETE FROM farm");
		 ps.executeUpdate();

		 try {
			ps.close();
		 } catch (final Exception e2) {
		 }

		 ps = connection.prepareStatement("DELETE FROM news");
		 ps.executeUpdate();

		 DBConnectionFactory.getConnectionHandler().resetAutoIncrement();
	  } catch (final SQLException e2) {
		 Logger.getLogger("Error").error("", e2);
	  } finally {
		 try {
			ps.close();
		 } catch (final Exception e3) {
		 }
	  }
	  return true;
   }

   /**
    * Closes the database connection
    */
   @Override
   public void close() {
	  try {
		 if (connection != null) {
			connection.close();
		 }
	  } catch (final SQLException e) {
		 Logger.getLogger("Error").error("", e);
	  }
   }

   /**
    * Checks whether the current connection is to an already setup database
    * 
    * @return
    */
   @Override
   public boolean connectionIsValid() {
	  if (connection == null)
		 return false;
	  PreparedStatement ps = null;
	  ResultSet rs = null;
	  boolean out = false;
	  try {
		 ps = connection.prepareStatement("SELECT * FROM settings");
		 rs = ps.executeQuery();
		 out = rs.next();
	  } catch (Exception e) {
		 if (!e.getMessage().equals("No database selected")) {
			Logger.getLogger("Error").error("", e);
		 }
	  } finally {
		 try {
			rs.close();
		 } catch (Exception e) {
		 }
		 try {
			ps.close();
		 } catch (Exception e) {
		 }
	  }
	  return out;
   }

   /**
    * returns a connection to the database
    * 
    * @return
    */
   @Override
   public Connection getConnection() {
	  return connection;
   }

   /**
    * Returns the current database version
    * 
    * @param con
    * @return
    */
   @Override
   public int getDBVersion() {
	  PreparedStatement ps = null;
	  ResultSet rs = null;
	  int out = 0;
	  try {
		 ps = connection.prepareStatement("SELECT * FROM version");
		 rs = ps.executeQuery();
		 if (rs.next()) {
			out = rs.getInt("dbversion");
		 }
	  } catch (final SQLException e) {
		 Logger.getLogger("Error").error("", e);
	  } finally {
		 try {
			rs.close();
		 } catch (final Exception e) {
		 }
		 try {
			ps.close();
		 } catch (final Exception e) {
		 }
	  }
	  return out;
   }

   /**
    * Loads the database driver and establishes a connection
    * 
    * @return
    */
   private Connection load() {
	  Connection out = null;
	  try {
		 Class.forName("com.mysql.jdbc.Driver").newInstance();
		 Statement stmt = null;
		 try {
			out = DriverManager.getConnection("jdbc:mysql://" + host + "/", user, password);
			stmt = out.createStatement();
			stmt.execute("use " + dbName);
		 } catch (SQLException e) {
			// it's possible that we've already been redirected to the correct db
		 } finally {
			try {
			   stmt.close();
			} catch (final Exception e) {
			}
		 }
	  } catch (InstantiationException e1) {
		 Logger.getLogger("Error").error("", e1);
	  } catch (IllegalAccessException e1) {
		 Logger.getLogger("Error").error("", e1);
	  } catch (ClassNotFoundException e1) {
		 Logger.getLogger("Error").error("", e1);
	  }
	  return out;
   }

   @Override
   /**
    * Performs a database update
    * 
    * @param con
    * @param botSettings
    * @return
    */
   public int performDBUpdate() {
	  Statement stmt = null;
	  BufferedReader br = null;
	  int out = 1;
	  try {
		 stmt = connection.createStatement();
		 InputStream is = getClass().getResourceAsStream("/database/mysqlupdate.sql");
		 br = new BufferedReader(new InputStreamReader(is));

		 String line;
		 String code = "";
		 boolean read = false;
		 int dbversion = getDBVersion();
		 while ((line = br.readLine()) != null) {
			if (read && !line.startsWith("#")) {
			   code += line;
			} else if (line.equals("#version " + dbversion)) {
			   read = true;
			}

		 }
		 final String[] split = code.split(";");

		 for (String s : split) {
			try {
			   stmt.execute(s);
			} catch (final Exception e) {
			}
		 }
		 out = 2;
	  } catch (final Exception e) {
		 Logger.getLogger("Error").error("", e);
	  } finally {
		 try {
			stmt.close();
		 } catch (final Exception e) {
		 }
		 try {
			br.close();
		 } catch (final Exception e) {
		 }
	  }
	  return out;
   }

   @Override
   public void resetAutoIncrement() {
	  PreparedStatement ps = null;
	  try {
		 ps = connection.prepareStatement("ALTER TABLE news AUTO_INCREMENT = 1");
		 ps.executeUpdate();

		 try {
			ps.close();
		 } catch (final Exception e2) {
		 }

		 ps = connection.prepareStatement("ALTER TABLE orders AUTO_INCREMENT = 1");
		 ps.executeUpdate();

		 try {
			ps.close();
		 } catch (final Exception e2) {
		 }

		 ps = connection.prepareStatement("ALTER TABLE aid AUTO_INCREMENT = 1");
		 ps.executeUpdate();

		 try {
			ps.close();
		 } catch (final Exception e2) {
		 }

		 ps = connection.prepareStatement("ALTER TABLE discussion AUTO_INCREMENT = 1");
		 ps.executeUpdate();

		 try {
			ps.close();
		 } catch (final Exception e2) {
		 }

		 ps = connection.prepareStatement("ALTER TABLE post AUTO_INCREMENT = 1");
		 ps.executeUpdate();

		 try {
			ps.close();
		 } catch (final Exception e2) {
		 }

		 ps = connection.prepareStatement("ALTER TABLE message AUTO_INCREMENT = 1");
		 ps.executeUpdate();
	  } catch (SQLException e) {
		 Logger.getLogger("Error").error("", e);
	  } finally {
		 try {
			ps.close();
		 } catch (final Exception e2) {
		 }
	  }
   }
}
